/* 
 * File:   consts.h
 * Author: Anand
 *
 * Created on February 23, 2009, 2:25 PM
 */


#ifndef _CONSTS_H
#define	_CONSTS_H

#define DBNAME "playlist.db"

#define GET_NEW_ID_SQL "Select max(id)+1 id from ?"


#define PLAYLIST_TABLE_NAME "playlist"
#define PLAYLIST_TABLE_CREATION_SQL "CREATE TABLE playlist(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(120), lastactivets INTEGER)"
#define PLAYLIST_GET_LATEST_SQL "SELECT * FROM playlist WHERE lastactivets = (SELECT MAX(lastactivets) FROM playlist)"
#define PLAYLIST_INSERT_SQL "INSERT INTO playlist (id, name, lastactivets) VALUES (?, '?',?)"
#define PLAYLIST_UPDATE_SQL "UPDATE playlist SET name = '?', lastactivets = ? WHERE id = ?"
#define PLAYLIST_GET_SQL "SELECT * FROM playlist WHERE id = ?"
#define PLAYLIST_GET_ALL_SQL "SELECT * FROM playlist"

#define FOLDER_TABLE_NAME "folder"
#define FOLDER_CREATION_SQL "CREATE TABLE folder (id INTEGER PRIMARY KEY AUTOINCREMENT, path VARCHAR(256))"
#define FOLDER_GET_SQL "SELECT * FROM folder WHERE path = '?'"
#define FOLDER_INSERT_SQL "INSERT INTO folder SELECT ?, '?'"

#define PLAYLISTITEM_TABLE_NAME "playlistitem"
#define PLAYLISTITEM_TABLE_CREATION_SQL "CREATE TABLE playlistitem (id INTEGER PRIMARY KEY AUTOINCREMENT,folderid INTEGER, name VARCHAR(100), playcount INTEGER)"
#define PLAYLISTITEM_GET_SQL "SELECT * FROM playlistitem WHERE folderid = ? AND name = '?'"
#define PLAYLISTITEM_UPDATE_SQL "UPDATE playlistitem SET playcount = ? WHERE id = ?"
#define PLAYLISTITEM_SAVE_SQL "INSERT INTO playlistitem  SELECT ? , ? , '?', ?"

#define PLAYLISTITEMS_TABLE_NAME "playlistitems"
#define PLAYLISTITEMS_TABLE_CREATION_SQL "CREATE TABLE playlistitems (playlistid INTEGER, playlistitemid INTEGER, position DECIMAL(10,3))"
#define PLAYLISTITEMS_GET_SQL "SELECT pis.position, f.path, pi.name, pi.id  FROM playlistitems pis JOIN playlist p ON pis.playlistid = p.id JOIN playlistitem pi ON pis.playlistitemid = pi.id JOIN folder f ON pi.folderid = f.id WHERE p.id = ? ORDER BY pis.position"
#define PLAYLISTITEMS_SAVE_SQL "INSERT INTO playlistitems SELECT ? ,?, ?"
#define PLAYLISTITEMS_UPDATE_SQL "UPDATE playlistitems SET position =  ? WHERE playlistid = ? AND playlistitemid = ?"
#define PLAYLISTITEMS_DELETE_SQL "DELETE playlistitems WHERE playlistid = ? AND playlistitemid = ?"

#endif	/* _CONSTS_H */

